import pymysql
from typing import List, Dict, Any, Optional
from dbutils.pooled_db import PooledDB
from fastapi import FastAPI
from pydantic import BaseModel

from app.config import settings
app = FastAPI(title="t_db_group CRUD API")

# ========== 连接池 ==========
POOL = PooledDB(
    creator=pymysql,
    maxconnections=10,   # 连接池最大连接数
    mincached=2,         # 初始化连接数
    maxcached=5,         # 连接池最大空闲连接数
    maxshared=3,         # 最大共享连接数
    blocking=True,       # 连接池满时是否等待
    host=settings.mysql_host,
    port=settings.mysql_port,
    user=settings.mysql_user,
    password=settings.mysql_password,
    database=settings.mysql_db,
    autocommit=True
)


def get_conn():
    return POOL.connection()

# ========== 数据模型 ==========
class DbGroupItem(BaseModel):
    id: int = 0  # 0 表示新增
    group_name: str
    databases: Optional[str] = None
    create_user: Optional[str] = None

class DeleteRequest(BaseModel):
    ids: List[int]

class ResponseModel(BaseModel):
    success: bool
    message: str
    data: Optional[List[dict]] = None

# ========== DAO ==========
class DbGroupDAO:
    def upsert(self, records: List[dict]):
        conn = get_conn()
        try:
            with conn.cursor() as cursor:
                for rec in records:
                    if rec.get("id", 0):  # 更新
                        sql = """
                            UPDATE t_db_group
                            SET group_name=%s, `databases`=%s, create_user=%s
                            WHERE id=%s
                        """
                        cursor.execute(sql, (
                            rec.get("group_name"),
                            rec.get("databases"),
                            rec.get("create_user"),
                            rec["id"]
                        ))
                    else:  # 新增
                        sql = """
                            INSERT INTO t_db_group (group_name, `databases`, create_user)
                            VALUES (%s, %s, %s)
                        """
                        cursor.execute(sql, (
                            rec.get("group_name"),
                            rec.get("databases"),
                            rec.get("create_user")
                        ))
        finally:
            conn.close()

    def delete(self, ids: List[int]):
        if not ids:
            return
        conn = get_conn()
        try:
            with conn.cursor() as cursor:
                sql = f"DELETE FROM t_db_group WHERE id IN ({','.join(['%s']*len(ids))})"
                cursor.execute(sql, ids)
        finally:
            conn.close()

    def list_all(self) -> List[dict]:
        conn = get_conn()
        try:
            with conn.cursor(pymysql.cursors.DictCursor) as cursor:
                cursor.execute("SELECT * FROM t_db_group ORDER BY id")
                return cursor.fetchall()
        finally:
            conn.close()